# Copyright (c) 2023, szufisher and contributors
# For license information, please see license.txt

import frappe
from frappe import _
from frappe.model.document import Document
from frappe.utils import flt,now
from frappe.query_builder.functions import Sum
from pypika import Tuple

class WarehouseTask(Document):
	def validate(self):
		self.validate_qty()
		self.validate_voucher_no()
	
	def enforce_pickup_before_putaway(self):		
		if self.voucher_type == "Stock Entry":
			purpose = frappe.db.get_value(self.voucher_type, self.voucher_no, "purpose")
			if purpose == "Material Transfer":
				pickup_wt = frappe.db.get_value("Warehouse Task",
					{"voucher_type": self.voucher_type,
					"voucher_no": self.voucher_no,
					"warehouse_task_type": "Pick Up"
					},
					["name","docstatus"],
					as_dict = 1
				)
				# 有下架单(发料仓启用了货位管理)，尚未提交则报错
				if pickup_wt is not None and pickup_wt.docstatus != 1:
					form_link = frappe.utils.get_link_to_form(self.doctype, pickup_wt.name)
					frappe.throw(_("Please submit pick up warehouse task {0}").format(form_link))

	def validate_voucher_no(self):
		existing_name = frappe.db.get_value(self.doctype,
			{'name': ('!=', self.name),
			 'voucher_type': self.voucher_type,
			 'voucher_no': self.voucher_no,
			 'warehouse_task_type': self.warehouse_task_type,
			 'docstatus': ('<', 2)
			}
		)
		if existing_name:
			form_link = frappe.utils.get_link_to_form(self.doctype, existing_name)
			frappe.throw(_('Voucher no {0} already in warehouse task {1}').format(
				self.voucher_no, form_link))


	def validate_qty(self):
		"""
		检验检查确保源单据的物料、仓库数量与上下架单拆分后的汇总数量匹配，上下架单中未手工删除明细行
		"""	

		source_item_wh_qty_map = {}
		item_wh_qty_map = {}
		if self.voucher_no:
			warehouse_field = 'warehouse'
			child_doctype = f'{self.voucher_type} Item'
			if self.voucher_type == 'Stock Entry':
				warehouse_field = 't_warehouse' if self.warehouse_task_type == 'Put Away' else 's_warehouse'
				child_doctype = f'{self.voucher_type} Detail'
			source = frappe.qb.DocType(child_doctype)		
			warehouse = frappe.qb.DocType('Warehouse')
			source_doc_qty_list = frappe.qb.from_(source
				).join(warehouse
				).on(source[warehouse_field] == warehouse.name
				).where(
					(source.parent == self.voucher_no) &
					(warehouse.enable_storage_bin == 1)
				).groupby(source[warehouse_field]
				).groupby(source.item_code
				).select(
					source[warehouse_field].as_('warehouse'),
					source.item_code,
					Sum(source.qty).as_('qty')
				).run(as_dict=1)
			
			for row in source_doc_qty_list:
				key = (row.item_code, row.warehouse)
				source_item_wh_qty_map.setdefault(key, 0)
				source_item_wh_qty_map[key] = source_item_wh_qty_map.get(key, 0) +row.qty

			for row in self.items:
				key = (row.item_code, row.warehouse)
				item_wh_qty_map.setdefault(key, 0)
				item_wh_qty_map[key] = item_wh_qty_map.get(key) + row.qty
			
			for (key, qty) in source_item_wh_qty_map.items():
				subtotal_qty = item_wh_qty_map.get(key,0)
				if flt(qty - subtotal_qty):
					msg = _('Item Code, warehouse {0} total qty {1} does not match qty {2} in original doc {3} {4}').format(
						key, subtotal_qty, qty, _(self.voucher_type), self.voucher_no
					)
					if self.docstatus == 0:
						frappe.msgprint(msg)
					else:
						frappe.throw(msg)

	def validate_storage_bin(self):
		no_storage_bin_rows = [row.idx for row in self.items if not row.storage_bin]
		if no_storage_bin_rows:
			frappe.throw(_("Rows missing storage bin : {0}").format(','.join(no_storage_bin_rows)))
		#todo storage bin在不同行上不允许重复	

	def on_submit(self):
		self.validate_storage_bin()
		self.enforce_pickup_before_putaway()
		self.update_storage_bin_qty()
		
	def on_cancel(self):
		self.update_storage_bin_qty()

	def update_storage_bin_qty(self):
		"""
		更新货位库存结余，
			1. 不存在就新增，首次新增加上过账日期与时间，用于后续先进先出
			2. 存在且变更后数量大于0就更新
			3. 存在且拣货后数量为0就删除
		"""

		def get_qty_map():
			no_batch = len(list(key_tuple)[0]) == 2
			sbq_doctype = frappe.qb.DocType('Storage Bin Qty')
			query = frappe.qb.from_(sbq_doctype
			).select(
				sbq_doctype.item_code,			
				sbq_doctype.batch_no,
				sbq_doctype.storage_bin,
				sbq_doctype.name,
				sbq_doctype.actual_qty.as_('qty')
			)
			if no_batch:
				query = query.where(Tuple(sbq_doctype.item_code, sbq_doctype.storage_bin).isin(key_tuple))
			else:
				query = query.where(Tuple(sbq_doctype.item_code, sbq_doctype.batch_no,sbq_doctype.storage_bin).isin(key_tuple))
			existing_qty_list = query.run(as_dict=1)
			existing_qty_map = {
				(row.item_code, row.storage_bin) if no_batch else (row.item_code, row.batch_no,row.storage_bin):
					{'name': row.name,
					 'qty': row.qty 
					}
				for row in existing_qty_list
			}

			return existing_qty_map
		
		#1.获取全部物料、仓库、批号、货位组合的现有结余数
		existing_qty_map = {}
		key_tuple = {(row.item_code, row.storage_bin) for row in self.items if not row.batch_no}
		if key_tuple:
			existing_qty_map.update(get_qty_map())
		key_tuple = {(row.item_code,row.batch_no,row.storage_bin) for row in self.items if row.batch_no}
		if key_tuple:
			existing_qty_map.update(get_qty_map())
		
		fields = [
			"item_code",
			"warehouse",
			"batch_no",
			"storage_bin",
			"actual_qty",
			"name",
			"owner",
			"modified_by",
			"creation",
			"modified"						
			]
		insert_list, update_list = [], []

		negative_factor = 1
		if self.warehouse_task_type == 'Pick Up':
			negative_factor *= -1
		if self.docstatus == 2:
			negative_factor *= -1

		for row in self.items:
			key = (row.item_code, row.batch_no, row.storage_bin) if row.batch_no else (row.item_code, row.storage_bin)
			existing = existing_qty_map.get(key)
			if not existing:
				#globals().update(locals())
				insert_list.append(
					[row.get(f) for f in fields[:4]]
				)
				insert_list[-1].extend([
					row.qty * negative_factor,
					frappe.generate_hash(length=10),
					frappe.session.user,
					frappe.session.user,
					now(),
					now()
				])
				if self.warehouse_task_type == 'Put Away':
					insert_list[-1].extend([
						self.posting_date,
						self.posting_time
					])
			else:
				qty = existing.get('qty')
				update_list.append(
					frappe._dict({'name': existing.get('name'),
					 'qty': qty + row.qty * negative_factor
					})
				)
		if self.warehouse_task_type == 'Put Away':
			fields.extend([
				"gr_date",
				"gr_time"
			])
		frappe.db.bulk_insert('Storage Bin Qty', fields, insert_list)
		for row in update_list:
			if row.get("qty", 0):
				frappe.db.set_value('Storage Bin Qty', row.name, 'actual_qty', row.get("qty", 0))

		if self.warehouse_task_type == 'Pick Up':
			# 删除下架后数量为0的行
			rows_to_delete = [row.name for row in update_list if not row.get("qty", 0)]
			if rows_to_delete:
				frappe.db.delete("Storage Bin Qty", {'name': ("in", rows_to_delete)})

def on_doctype_update():
	frappe.db.add_index("Warehouse Task Detail", 
		["item_code",
		#warehouse 是货位的属性，无需作为联合主键
		"batch_no",
		"storage_bin"]
	)

@frappe.whitelist()
def get_storage_bin(doctype=None, txt=None, searchfield=None, start=None, page_len=None, filters=None):
	"""
	通过过滤条件：仓库，禁用，剔除其它行已选货位
	上架：如果货位仅限上架至空货位，剔除已有库存的货位
	下架：限有库存且数量>=当前行需要的数量
	"""

	item_code = filters.get('item_code')
	warehouse = filters.get('warehouse')
	batch_no = filters.get('batch_no')
	stock_qty = filters.get('stock_qty', 0)
	warehouse_task_type = filters.get('warehouse_task_type')

	or_filters = {}
	if txt:
		for f in ['name', 'storage_bin_name']:
			or_filters[f] = ('like', f'%{txt}%')
	filters = {
		'warehouse': warehouse,
		'disabled': 0
	}
	storage_bin_used = filters.get('storage_bin_used')
	if storage_bin_used:
		storage_bin_used = json.loads(storage_bin_used)
		filters.name = ('not in', storage_bin_used)
	data = frappe.get_all('Storage Bin',
		filters = filters,
		or_filters = or_filters,
		fields = ['name', 'storage_bin_name','put_away_to_empty_bin']
		#order_by = 'idx'
	)
	globals().update(locals())
	bin_qty_map = {}
	key_tuple_no_batch = {(item_code, d.name) for d in data if not d.batch_no}  #warehouse 是货位的属性，无需作为联合主键
	if key_tuple_no_batch:
		bin_qty_map = get_bin_qty_map(key_tuple_no_batch, warehouse_task_type, stock_qty)
	key_tuple_with_batch = {(item_code, d.batch_no, d.name) for d in data if d.batch_no}
	if key_tuple_with_batch:  
		bin_qty_map.update(get_bin_qty_map(key_tuple_no_batch, warehouse_task_type, stock_qty))

	result = []
	if warehouse_task_type == 'Put Away':
		for d in data:
			if not d.put_away_to_empty_bin or d.name not in bin_qty_map:
				result.append([d.name, d.storage_bin_name])
	else:
		for d in data:
			existing_qty = bin_qty_map.get(d.name, 0)
			if existing_qty >= stock_qty:
				result.append([d.name, d.storage_bin_name])
	
	return result

def get_bin_qty_map(key_tuple, warehouse_task_type, stock_qty):
	sbq_doctype = frappe.qb.DocType('Storage Bin Qty')
	query = frappe.qb.from_(sbq_doctype
	).select(		
		sbq_doctype.storage_bin,		
		sbq_doctype.actual_qty.as_('qty')
	)
	if len(list(key_tuple)[0]) == 2:
		query = query.where(Tuple(sbq_doctype.item_code, sbq_doctype.storage_bin).isin(key_tuple))
	else:
		query = query.where(Tuple(sbq_doctype.item_code, sbq_doctype.batch_no,sbq_doctype.storage_bin).isin(key_tuple))
	if warehouse_task_type == 'Pick Up':
		query = query.where(sbq_doctype.actual_qty >= stock_qty)

	bin_qty_list = query.run(as_dict=1)
	bin_qty_map = {
		row.storage_bin: row.qty			
		for row in bin_qty_list
	}
	return bin_qty_map or {}